import pandas as pd
import numpy as np

#### DataFrame 合并

# df1  = pd.DataFrame(data = {"score": [80,70,60,50], "name1": ["小明", "小红", "小刚", "小华"]})
# df2  = pd.DataFrame(data = {"score": [70,60,50,40], "name2": ["李明", "李华", "张兵", "王军"]})
#
# df = pd.merge(df1, df2)
# print(df)


# df = pd.merge(df1, df2, on = "score",suffixes = ["_一班", "_二班"], how = "outer")
# print(df)


# ## contact
# df1  = pd.DataFrame(data = {"score": [80,70,60,50], "name1": ["小明", "小红", "小刚", "小华"]})
# df2  = pd.DataFrame(data = {"score": [70,60,50,40], "name2": ["李明", "李华", "张兵", "王军"]})
# #默认axis= 0，纵向合并
# data1 = pd.concat([df1,df2])
# print(data1)
#
# #axis= 1,横向合并
# data2 = pd.concat([df1,df2], axis= 1)
# print("原来的data:\n", data2)
# data2 = pd.concat([df1,df2], axis= 1, ignore_index= True)
# print("ignore_index= True :\n", data2)

# ## join
# df1  = pd.DataFrame(data = {"score": [80,70,60,50], "name1": ["小明", "小红", "小刚", "小华"]})
# df2  = pd.DataFrame(data = {"score": [70,60,50,40], "name2": ["李明", "李华", "张兵", "王军"]})
#
# # join默认是outer
# data1 = pd.concat([df1,df2])
# print(data1)
#
# # join= "inner"
# data2 = pd.concat([df1, df2],join= "inner")
# print(data2)


# ## 算数的四则运算
# df1 = pd.DataFrame([np.arange(0,4),np.arange(1,5),np.arange(2,6),np.arange(2,6)])
# df2 = pd.DataFrame([np.arange(1,5),np.arange(0,4),np.arange(3,7)])
# print("df1:\n",df1)
# print("df2:\n",df2)
# print("df1+df2:\n",df1+df2)

# df1  = pd.DataFrame(data = {"score": [80,70,60,50], "name1": ["小明", "小红", "小刚", "小华"]})
# df2  = pd.DataFrame(data = {"score": [70,60,50,40], "name2": ["李明", "李华", "张兵", "王军"]})
# print("df1:\n",df1)
# print("df2:\n",df2)
# print('df1["score"] - df2["score"]:\n',df1["score"] - df2["score"])


###  算数与比较运算

# df1 = pd.DataFrame([np.arange(0,4),np.arange(1,5),np.arange(2,6)])
# df2 = pd.DataFrame([np.arange(1,5),np.arange(0,4),np.arange(3,7)])
# print("df1:\n",df1)
# print("df2:\n",df2)
# print("df1>df2:\n",df1>df2)


# df1  = pd.DataFrame(data = {"score": [80,70,60,50], "name1": ["小明", "小红", "小刚", "小华"]})
# df2  = pd.DataFrame(data = {"score": [70,60,50,40], "name2": ["李明", "李华", "张兵", "王军"]})
#
# print(df1["score"]>df2["score"])

## 排序

# arr = np.arange(12).reshape(3,4)
# df = pd.DataFrame(data=arr, index = ["a",'b','c'], columns= ["a","b","c","d"])
# df.sort_index(axis = 1)
# print("df.sort_index(axis = 1):\n", df)



# df1  = pd.DataFrame(data = {"score": [80,70,60,50], "name1": ["小明", "小红", "小刚", "小华"]})
# df2  = pd.DataFrame(data = {"score": [70,60,50,40], "name2": ["李明", "李华", "张兵", "王军"]})
#
# df = df1.sort_values(by = "score",ascending = False)
# print("score这一列排序，降序：\n",df)

##  数据统计


# df1  = pd.DataFrame(data = {"score": [80,70,60,50], "name": ["小明", "小红", "小刚", "小华"]})
# df2  = pd.DataFrame(data = {"score": [70,60,50,40], "name": ["李明", "李华", "张兵", "王军"]})
#
# #合并
# df = pd.concat([df1, df2])
# print(df)
# #单独打印最大值
# print("score 的最大值：",df["score"].sum())
#
# #针对各列的统计汇总
# print("针对各列的统计汇总: \n",df.describe())

# df1  = pd.DataFrame(data = {"score1": [80,70,60,50],"score2": [70,90,50,40], "name": ["小明", "小红", "小刚", "小华"]})
# df2  = pd.DataFrame(data = {"score1": [70,60,50,40], "score2": [80,70,90,50],"name": ["李明", "李华", "张兵", "王军"]})
#
# #合并
# df = pd.concat([df1,df2])
# #使用corr函数
# print("corr函数：\n",df["score1"].corr(df["score2"]))

## 特殊的统计函数

##前几行的和/积
# arr = np.arange(12).reshape(4,3)
# df = pd.DataFrame(data = arr)
# print(df)
# print("")
# print("df.cumsum():\n",df.cumsum())
# print("")
# print("df.cumprod():\n",df.cumprod())

##前几行的最大值和最小值
# arr = np.arange(12).reshape(4,3)
# df = pd.DataFrame(data = arr)
# print(df)
# print("")
# ##最大值
# print("df.cummax():\n",df.cummax())
# print("")
#
# #最小值
# print("df.cummin():\n",df.cummin())

## 检查和处理数据是否有空值

##检查空值
# data = {
#     "name":["小李","小红","小明","小刚"],
#     "age":[18,None,18,20],
#     "class":[None,2,None,3]
# }
# #创建一个DataFrame
# df = pd.DataFrame(data)
# print(df)
# print(".isnull(), 以表格形式打印空值 ：\n",df.isnull())
# print(".isnull().sum() 统计每行的空值的个数:\n", df.isnull().sum())
# print(".isnull().sum().sum() 统计全部的空值的个数:\n", df.isnull().sum().sum())

# ##填充空值
# data = {
#     "name":["小李","小红","小明","小刚"],
#     "age":[18,None,18,20],
#     "class":[None,2,None,3]
# }
# #创建一个DataFrame
# df = pd.DataFrame(data)
# print(df)
# # df1 = df.fillna(0,inplace=True) #这里的inplace参数会直接改变原df的值
# df1 = df.fillna(0)
# print(".fillna(0), 把全部空值填为0：\n" ,df1)
# df2 = df.ffill()
# print("ffill(), 用前一行对应的数值填充：\n",df2)


##滚动计算
# arr = np.arange(12).reshape(4,3)
# df = pd.DataFrame(data = arr)
# print(df)
# print("")
# print(df.rolling(2).sum())

##分组和聚合
# data = {'A':["数学", "语文","数学", "英语","数学", "语文","英语"],
#         'B':[2001,2003,2005,2003,2001,2005,2004],
#         'C':np.arange(7,14)}
# df = pd.DataFrame(data = data)
# ## 把A分组
# A_group = df.groupby(["A","B"])
# print("使用A的分组, A_group.sum():\n",A_group.count())
# ## 把B分组
# B_group = df.groupby("B")
# print("使用B的分组, B_group.sum():\n",B_group.count())

# ##分组和聚合
# data = {'A':["数学", "语文","数学", "英语","数学", "语文","英语"],
#         'B':[2001,2003,2005,2003,2001,2005,2004],
#         'C':np.arange(7,14)}
# df = pd.DataFrame(data = data)
# ## 把A和B同时分组
# AB_group = df.groupby(["A","B"])
# print("使用AB的分组, AB_group.sum():\n",AB_group.count())
#
#
#
#
# # data = {'A':["数学", "语文","数学", "英语","数学", "语文","英语"],
# #         'B':[2001,2003,2005,2003,2001,2005,2004],
# #         'C':np.arange(7,14)}
# # df = pd.DataFrame(data = data)
# #
# # print("在行上进行聚合：\n",df.agg(["A","B"]))

## 透视表

# ## 设置一个学校成绩表，里面包含了每人的成绩和班级
# data = {
#
#         "score":[93,95,92,91,96,98],
#         "name":["小明","小华","小红","小东","小刚","小强"],
#         "class":[3,2,1,1,2,1],
# }
#
# sort_values("score",ascending = False) 把分数按降序排序
# reset_index(drop = True)  把索引设置为0可是的升序
# df = pd.DataFrame(data= data).sort_values("score",ascending = False).reset_index(drop = True)
# print(df)
#
# #班级作为行进行聚合，列为score，aggafun默认是平均值: 打印每班级的平均值
# p1 = df.pivot_table(["score"],index = ["class"])
# print('班级作为行进行聚合，列为score，aggafun默认是平均值:\n',p1)
#
# #班级作为行进行聚合，列为score，aggafun= max: 打印每班级的最大值
# p2 = df.pivot_table(["score"],index = ["class"],aggfunc = "max")
# print('班级作为行进行聚合，列为score，aggafun= max:\n',p2)
#
#

# ## 设置一个学校成绩表，里面包含了每人的成绩和班级
# data = {
#         "score1":[93,95,92,91,96,98],
#         "score2":[93,9,92,91,96,98],
#         "name":["小明","小华","小红","小东","小刚","小强"],
#         "class":[3,2,1,1,2,1],
# }
#
# #sort_values("score1",ascending = False) 把分数按降序排序
# #reset_index(drop = True)  把索引设置为0可是的升序
# df = pd.DataFrame(data= data).sort_values("score1",ascending = False).reset_index(drop = True)
# print(df)
#
# # #班级作为行进行聚合，列为score，aggafun= {"score1": "max","score2": "mean"}: 同时打印每班级的最大值和平均值
# p3 = df.pivot_table(["score1","score2"],index = ["class"],aggfunc ={"score1": "max","score2": "mean"})
# print('班级作为行进行聚合，列为score，aggafun={"score1": "max","score": "mean"}:\n',p3)




# ## 设置一个学校成绩表，里面包含了每人的成绩和班级
# data = {
#         "score":[93,95,92,91,96,98],
#         "name":["小明","小华","小红","小东","小刚","小强"],
#         "class":[3,2,1,1,2,1],
# }
#
# # sort_values("score1",ascending = False) 把分数按降序排序
# # reset_index(drop = True)  把索引设置为0可是的升序
# df = pd.DataFrame(data= data).sort_values("score",ascending = False).reset_index(drop = True)
# print(df)
#
# #班级和姓名作为行进行聚合，列为score，aggafun默认: 打每班级各个人集合在一起，并打印每人的成绩
# p2 = df.pivot_table(["score"],index = ["class","name"])
# print('班级和姓名作为行进行聚合，列为score，aggafun默认:\n',p2)


# ## 交叉表
#
# ## 设置一个学校成绩表，里面包含了每人的成绩和班级
# data = {
#         "score":[93,95,92,91,95,98],
#         "name":["小明","小华","小红","小东","小刚","小强"],
#         "class":[3,2,1,1,2,1],
# }
#
# # sort_values("score1",ascending = False) 把分数按降序排序
# # reset_index(drop = True)  把索引设置为0可是的升序
# df = pd.DataFrame(data= data).sort_values("score",ascending = False).reset_index(drop = True)
# print(df)
# print("")
# # 在每个班级中各个分数段占的百分比
# p = pd.crosstab(index = df["class"], columns =df["score"],normalize = True, margins = True)
# print("normalize = True,margins = True;在每个班级中各个分数段有多少人:\n",p)


